/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.fandi.dao.impl;

import com.fandi.config.DatabaseConnection;
import com.fandi.dao.CustomerDao;
import com.fandi.model.CustomerModel;
import com.fandi.util.DateFormatterUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Fandy
 */
public class CustomerDaoImpl implements CustomerDao {
    private final Connection conn;
    
    private final String insert     = "INSERT INTO tb_customer VALUES(null, ?, ?, ?, ?, ?, ?, now())";
    private final String update     = "UPDATE tb_customer SET code=?, customer_name=?, address=?, phone=?, email=?, description=?, last_upd=now() WHERE uid=?";
    private final String delete     = "DELETE FROM tb_customer WHERE code=?";
    private final String select     = "SELECT * FROM tb_customer";
    private final String selectBy   = "SELECT * FROM tb_customer WHERE code=?";
    private final String search     = "SELECT * FROM tb_customer WHERE customer_name LIKE '%' ? '%' ";
    private final String count      = "SELECT COUNT(*) as totalRecord FROM tb_customer";

    public CustomerDaoImpl() {
        conn = DatabaseConnection.getConnection();
    }
    
    @Override
    public void insert(CustomerModel model) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(insert)) {
            ps.setString(1, model.getCode());
            ps.setString(2, model.getName());
            ps.setString(3, model.getAddress());
            ps.setString(4, model.getPhone());
            ps.setString(5, model.getEmail());
            ps.setString(6, model.getDescription());
            
            ps.executeUpdate();
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }

    @Override
    public void update(CustomerModel model) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(update)) {
            ps.setString(1, model.getCode());
            ps.setString(2, model.getName());
            ps.setString(3, model.getAddress());
            ps.setString(4, model.getPhone());
            ps.setString(5, model.getEmail());
            ps.setString(6, model.getDescription());
            ps.setInt(7, model.getId());
            
            ps.executeUpdate();
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }

    @Override
    public boolean delete(String code) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(delete)) {
            ps.setString(1, code);
            
            ps.executeUpdate();
            
            return true;
        } catch (SQLException se) {
            se.printStackTrace();
            return false;
        }
    }

    @Override
    public List<CustomerModel> selectAll() throws SQLException {
        List<CustomerModel> list = new ArrayList<>();
        
        try (
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(select)
            ) {
            
            while (rs.next()) {
                CustomerModel model = new CustomerModel();
                model.setId(rs.getInt("uid"));
                model.setCode(rs.getString("code"));
                model.setName(rs.getString("customer_name"));
                model.setAddress(rs.getString("address"));
                model.setPhone(rs.getString("phone"));
                model.setEmail(rs.getString("email"));
                model.setDescription(rs.getString("description"));
                model.setLastUpd(rs.getDate("last_upd"));

                list.add(model);
            }

            return list;
        } catch (SQLException se) {
            se.printStackTrace();
            return null;
        }
    }

    @Override
    public CustomerModel selectBy(String code) throws SQLException {
        CustomerModel model = null;
        
        try (
            PreparedStatement ps = conn.prepareStatement(selectBy);
            ) {
            
            ps.setString(1, code);
            ResultSet rs = ps.executeQuery();
            
            if (rs.next()) {
                model = new CustomerModel();
                model.setId(rs.getInt("uid"));
                model.setCode(rs.getString("code"));
                model.setName(rs.getString("customer_name"));
                model.setAddress(rs.getString("address"));
                model.setPhone(rs.getString("phone"));
                model.setEmail(rs.getString("email"));
                model.setDescription(rs.getString("description"));
                model.setLastUpd(rs.getDate("last_upd"));
            }

            return model;
        } catch (SQLException se) {
            se.printStackTrace();
            return null;
        }
    }

    @Override
    public List<CustomerModel> searchBy(String code) throws SQLException {
        List<CustomerModel> list = new ArrayList<>();
        PreparedStatement ps = null;
        
        try /*(
                PreparedStatement ps = conn.prepareStatement(search);
            )*/ {
            ps = conn.prepareStatement(search);
            ps.setString(1, code);
            
            ResultSet rs = ps.executeQuery();
            
            while (rs.next()) {
                CustomerModel model = new CustomerModel();
                model.setId(rs.getInt("uid"));
                model.setCode(rs.getString("code"));
                model.setName(rs.getString("customer_name"));
                model.setAddress(rs.getString("address"));
                model.setPhone(rs.getString("phone"));
                model.setEmail(rs.getString("email"));
                model.setDescription(rs.getString("description"));
                model.setLastUpd(rs.getDate("last_upd"));

                list.add(model);
            }
            
            return list;
        } catch (SQLException se) {
            se.printStackTrace();
            return null;
        }
    }

    @Override
    public int count() throws SQLException {
        PreparedStatement ps = null;
        int totalRows = 0;
        
        String today = DateFormatterUtil.formatNow("", new java.util.Date());
        
        try {
            
            ps = conn.prepareStatement(count);
            
            ResultSet rs = ps.executeQuery();
            
            if (rs.next()) {
                totalRows = rs.getInt("totalRecord");
            }
            
            return totalRows;
        } catch (SQLException se) {
            se.printStackTrace();
            return 0;
        }
    }
    
}
